﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using Northwind.NET.BLL;


namespace Northwind.NET.Testing.Console
{
    [TestFixture]
    public class TestSet_0001_EDMTests
    {
        [SetUp]
        public void Setup() { }

        [TearDown]
        public void TearDown() { }


        [Test]
        public void TestQuery6()
        {
            var result6 = CustomersStatistics.QuarterlyOrders(1); 

            int index = 0;
            foreach (var c in result6)
            {
                 System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}", c.Name, c.ProductName, c.Q1Total, c.Q2Total, c.Q3Total, c.Q4Total, c.TotalCost);

                if (index++ == 10) break;
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void TestQuery5()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //select 
            //c.ID as CustomerId, c.ContactName, c.Name, c.City, C.Country,
            //YEAR(o.OrderDate)as [YearNum], 
            //'QuarterNum' = CASE 
            //  when MONTH(ShippedDate) between 1 and 3 then 1
            //  when MONTH(ShippedDate) between 4 and 6 then 2
            //  when MONTH(ShippedDate) between 7 and 9 then 3
            //  when MONTH(ShippedDate) between 10 and 12 then 4
            //END,
            //p.ID as ProductId, p.Name as ProductName,
            //o.OrderDate,
            //d.Quantity * d.UnitPrice* (1-d.Discount) as ProductCost
            //from
            //Customer c, [Order] o, OrderDetail d, Product p
            //where
            //c.ID = o.CustomerId and
            //o.Id = d.OrderId and
            //d.ProductId = p.Id

            var result1 = from c in _context.Customers
                          join o in _context.Orders
                          on c.CustomerID equals o.CustomerID
                          join d in _context.Order_Details
                          on o.OrderID equals d.OrderID
                          join p in _context.Products
                          on d.ProductID equals p.ProductID
                          select new 
                           { 
                                       CustomerId = c.CustomerID, c.ContactName, c.ContactTitle, c.City, c.Country,
                                       YearNum = (o.OrderDate.HasValue)?((DateTime)o.ShippedDate).Year:-1,
                                       MonthNum = (o.OrderDate.HasValue) ? ((DateTime)o.ShippedDate).Month : -1,
                                       ProductId = p.ProductID, ProductName = p.ProductName,
                                       o.ShippedDate,
                                       ProductCost = d.Quantity * (float?)d.UnitPrice*(1-d.Discount)  
                           };
            var result2 = from r in result1
                          select new
                          {
                              r.CustomerId, r.ContactName, r.ContactTitle, r.City, r.Country,
                              r.YearNum, r.MonthNum, 
                              QuarterNum = (r.MonthNum >=1 && r.MonthNum <=3)? "1":
                                           (r.MonthNum >= 4 && r.MonthNum <= 6) ? "2" :
                                           (r.MonthNum >= 7 && r.MonthNum <= 9) ? "3" :
                                           (r.MonthNum >= 10 && r.MonthNum <= 12) ? "4" : "-1",
                              r.ProductId, r.ProductName,
                              r.ShippedDate, r.ProductCost  
                          };

             var result3 = from r in result2
                          group r by r.ContactName + "-"+ r.ProductName + "-" + r.QuarterNum into g
                          select new
                          {
                              GroupKey = g.Key, 
                              CustomerId = g.Min(r=>r.CustomerId), 
                              Name = g.Min(r=>r.ContactName), 
                              ContactName = g.Min(r=>r.ContactName), 
                              ProductId = g.Min(r=>r.ProductId),
                              ProductName = g.Min(r=>r.ProductName),
                              QuarterNum = g.Min(r=>r.QuarterNum),
                              TotalCost = g.Sum(r=>r.ProductCost)
                          };

            var result4 = from r in result3
                          select new
                          {
                              r.GroupKey,
                              r.CustomerId,
                              r.Name,
                              r.ContactName,
                              r.ProductId,
                              r.ProductName,
                              r.QuarterNum,
                              r.TotalCost,
                              Q1Sum = (r.QuarterNum == "1") ? r.TotalCost : 0,
                              Q2Sum = (r.QuarterNum == "2") ? r.TotalCost : 0,
                              Q3Sum = (r.QuarterNum == "3") ? r.TotalCost : 0,
                              Q4Sum = (r.QuarterNum == "4") ? r.TotalCost : 0,

                          };

            var result5 = from r in result4
                          group r by r.GroupKey into g 
                          select new
                          {
                              GroupKey = g.Key,
                              CustomerId = g.Min(r => r.CustomerId),
                              Name = g.Min(r => r.Name),
                              ContactName = g.Min(r => r.ContactName),
                              ProductId = g.Min(r => r.ProductId),
                              ProductName = g.Min(r => r.ProductName),
                              Q1Total = g.Sum(r => r.Q1Sum),
                              Q2Total = g.Sum(r => r.Q2Sum),
                              Q3Total = g.Sum(r => r.Q3Sum),
                              Q4Total = g.Sum(r => r.Q4Sum),
                              TotalCost = g.Sum(r => r.Q1Sum + r.Q2Sum + r.Q3Sum + r.Q4Sum)
                          };

            var result6 = from r in result5
                          orderby r.CustomerId ascending, r.Name ascending, r.ProductName ascending
                          select new
                          {
                              r.GroupKey,
                              r.CustomerId,
                              r.Name,
                              r.ContactName,
                              r.ProductId,
                              r.ProductName,
                              r.Q1Total,
                              r.Q2Total,
                              r.Q3Total,
                              r.Q4Total,
                              r.TotalCost
                          };

            int index = 0;
            foreach (var c in result6)
            {
                //System.Console.WriteLine("{0} {1} {2} {3} {4} {5}", c.CustomerId, c.Name, c.City, c.QuarterNum);
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}", c.Name, c.ProductName, c.Q1Total, c.Q2Total, c.Q3Total, c.Q4Total, c.TotalCost);

                if (index++ == 10) break;
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }


        [Test]
        public void TestQuery4()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();

            //bool ret = Int32.TryParse(txtOrderId.Text, out orderId);
            int orderId = 10250;

            var result1 = from orderDetail in _context.OrderDetail
                         join order in _context.Order
                         on orderDetail.OrderID equals order.ID
                         let freight = order.Freight 
                         where order.ID == orderId
                         group orderDetail by order.ID into g
                         select new
                         {
                             OrderId = g.Key,
                             ExtendedPrice = g.Sum(orderDetail=>(float?)orderDetail.UnitPrice * orderDetail.Quantity * (1 - orderDetail.Discount)),
                         };
            var result = (from row in result1
                         join order in _context.Order
                         on row.OrderId equals order.ID
                         select new
                         {
                             row.OrderId,
                             row.ExtendedPrice,
                             order.Freight,
                             TotalPrice = row.ExtendedPrice + (float?)order.Freight
                         }).FirstOrDefault() ;


            //foreach (var item in result)
            {
                System.Console.WriteLine("{0} {1} {2} {3}", result.OrderId, result.ExtendedPrice, result.Freight, result.TotalPrice);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void TestQuery3()
        {
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            
            //bool ret = Int32.TryParse(txtOrderId.Text, out orderId);
            int orderId = 10248;
            var result = from o in _context.OrderDetail
                         join p in _context.Product
                         on o.ProductID equals p.ID
                         where o.OrderID == orderId
                         select new
                         {
                             o.ID,
                             o.ProductID,
                             ProductName = p.Name,
                             o.UnitPrice,
                             o.Quantity,
                             o.Discount,
                             ExtendedPrice = (float?)o.UnitPrice * o.Quantity * (1 - o.Discount)
                         };
            foreach (var item in result)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}", item.ID, item.ProductID, item.ProductName, item.UnitPrice, item.Quantity,
                                                    item.Discount, item.ExtendedPrice);
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void TestQuery2()
        {
            int employeeId = 1;
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var employees = (from e in _context.Employee
                            where e.ID == employeeId
                             select new { e.ID, FullName = e.LastName + " " + e.FirstName} )  ;
            foreach (var employee in employees)
            {
                System.Console.WriteLine("{0} {1}", employee.ID, employee.FullName);    
            }

            //var employees1 = (from e1 in _context.Employee
            //                 select new { e1.ID, FullName = e1.LastName + " " + e1.FirstName });


            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void TestQuery1()
        {
            int customerId = 1;
            NorthwindNETEntities _context = Globals.CreateObjectContext();
            var customer = (from c in _context.Customer
                           where c.ID == customerId
                           select c).SingleOrDefault<Customer>();
            if (customer != null)
            {
                System.Console.WriteLine("{0} {1} {2} {3} {4}", customer.Address, customer.Region, customer.City,
                                             customer.PostalCode, customer.Country);
            }
            else
            {
                System.Console.WriteLine("NULL");  
            }

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }

        [Test]
        public void DynamicDataSet()
        {
            string test1 = "[" + Northwind.NET.Settings.Resources.DecodeResourceString(Northwind.NET.Settings.Resources.UnknownUserControlName) + "]";
            System.Console.WriteLine(test1);
            string test2 = string.Format(Northwind.NET.Settings.Resources.DecodeResourceString(Northwind.NET.Settings.Resources.UnknownUserControlName), "TEST");
            System.Console.WriteLine(test2);
  
            //
            // TODO - it doesn't work this way
            //
            //NorthwindNETEntities : ObjectContext

            // Test 'Northwind.NET.Testing.Console.TestSet_0001_EDMTests.DynamicDataSet' 
            // failed: System.ArgumentException : The provided EntitySet name must be qualified 
            // by the EntityContainer name, such as 'EntityContainerName.EntitySetName',
            // or the DefaultContainerName property must be set for the ObjectContext.
            ObjectContext entities = new Northwind.NET.Model.NorthwindNETEntities();
            var dataSet = entities.CreateObjectSet<Model.Supplier>();

            string entitySetName = entities.CreateObjectSet<Model.Supplier>().EntitySet.Name;
            string entityContainerName = entities.CreateObjectSet<Model.Supplier>().EntitySet.EntityContainer.Name ;

            System.Console.WriteLine("{0}.{1}", entityContainerName, entitySetName);

            var dataSet1 = entities.CreateObjectSet<EntityObject>("NorthwindNETEntities.Supplier");

            bool ret = true;
            Assert.IsTrue(ret, string.Format(Northwind.NET.Settings.Resources.TestFailedMessage, System.Reflection.MethodInfo.GetCurrentMethod().Name));
        }
    }
}
